**************************** PREPARE COVARIATES ********************************


*--- ASHE data on earnings
program prepare_ashe_data
	import excel "0 - Shared Appreciation\Data\nomis_2018_04_06_`1'_earnings.xlsx", ///
		sheet("Data") cellrange(A9:AH397) firstrow clear

	foreach x of varlist _all {
		local label : variable label `x'
		if regexm("`label'", "^20") {
			rename `x' year_`label'
		}
	}	
	
	rename B lacode
	keep localauthority year_* lacode
	drop if mi(lacode)

	reshape long year_, i(localauthority) j(year)
	destring year_, replace force
	
	gen d_`1'_e = (year_ - year_[_n-1] ) / year_[_n-1] * 100 if lacode == lacode[_n-1]
	
	gen year_current = year
end
*program drop prepare_ashe_data
prepare_ashe_data mean
save "0 - Shared Appreciation\Data\ashe_mean", replace

prepare_ashe_data median
rename year_ median_year_
merge 1:1 localauthority year using "0 - Shared Appreciation\Data\ashe_mean", nogen
rename year_ mean_year_
compress
save "0 - Shared Appreciation\Data\ashe_earnings", replace


*--- Land Registry data on local house prices
import delimited "${datadir_hpindex}\UK-HPI-full-file-2017-07.csv", clear 
gen dated = date(date, "DMY")
gen year = year(dated)

collapse index, by(year areacode)
gen year_current = year

save "0 - Shared Appreciation\data\local_price_level", replace


